package com.zss.poi;

import com.zss.pojo.Accountant;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.List;
import java.util.UUID;

public class AccountantPOIUtils {

    private static final String UPLOAD_DIR = "/excel/";

    public static void export(HttpServletResponse response, List<Accountant> list, int sumPrice) {

        try {

            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/accountant.xlsx");
            InputStream inputStream = resource.getInputStream();

//            File file = ResourceUtils.getFile("classpath:excel/expenditureStatisticsExport.xlsx");
//            FileInputStream inputStream = new FileInputStream(file);
            Workbook wb = new XSSFWorkbook(inputStream);

            CellStyle style = wb.createCellStyle();
            // 设置边框样式
            style.setBorderBottom((short) 1);
            style.setBorderBottom((short) 1);
            style.setBorderLeft((short) 1);
            style.setBorderRight((short) 1);


            Sheet sheetAt = wb.getSheetAt(0);
            for (int i = 0; i < list.size(); i++) {
                Row row = sheetAt.createRow(i+1);
                Accountant accountant = list.get(i);


                CreationHelper createHelper = wb.getCreationHelper();
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(
                        createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
                Cell cell = row.createCell(0);
                int year = accountant.getCreateTime().getYear()+1900;
                int month = accountant.getCreateTime().getMonth()+1;
                System.out.println(accountant.getCreateTime().getDate());
                LocalDate today = LocalDate.of(year,month,accountant.getCreateTime().getDate());// 获取当前日期
                cell.setCellValue(today.toString());
                cell.setCellStyle(cellStyle);


                row.createCell(1).setCellValue(accountant.getAname());
//                row.createCell(2).setCellValue(accountant.getPrice().intValue());

                // 应用样式到单元格
                row.getCell(0).setCellStyle(style);
                row.getCell(1).setCellStyle(style);
                row.getCell(2).setCellStyle(style);

            }
            CellRangeAddress region = new CellRangeAddress(list.size()+1, list.size()+1, 1, 2); // 合并第一行第一列到第二列
            sheetAt.addMergedRegion(region);
            Row row = sheetAt.getRow(list.size()+1);
            row.getCell(0).setCellValue("金额总计：");
            row.getCell(1).setCellValue(sumPrice);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);

            // 创建一个字体
            Font font = wb.createFont();
            font.setBold(true); // 设置字体加粗

            // 创建一个单元格样式，并将字体应用于它
            short cellIndex = 1;
            style.setFont(font);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    public static void detailExport(HttpServletResponse response, List<Accountant> list, int sumPrice) {


        try {

            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/accountant.xlsx");
            InputStream inputStream = resource.getInputStream();

            Workbook wb = new XSSFWorkbook(inputStream);

            CellStyle style = wb.createCellStyle();
            // 设置边框样式
            style.setBorderBottom((short) 1);
            style.setBorderBottom((short) 1);
            style.setBorderLeft((short) 1);
            style.setBorderRight((short) 1);

            Sheet sheetAt = wb.getSheetAt(0);
            for (int i = 0; i < list.size(); i++) {
                Row row = sheetAt.createRow(i+1);
                Accountant accountant = list.get(i);

                CreationHelper createHelper = wb.getCreationHelper();
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(
                        createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
                Cell cell = row.createCell(0);
                int year = accountant.getCreateTime().getYear()+1900;
                int month = accountant.getCreateTime().getMonth()+1;
                System.out.println(accountant.getCreateTime().getDate());
                LocalDate today = LocalDate.of(year,month,accountant.getCreateTime().getDate());// 获取当前日期
                cell.setCellValue(today.toString());
                cell.setCellStyle(cellStyle);


                row.createCell(1).setCellValue(accountant.getAname());
//                row.createCell(4).setCellValue(expenditure.getPrice().intValue());



                // 应用样式到单元格
                row.getCell(0).setCellStyle(style);
                row.getCell(1).setCellStyle(style);
                row.getCell(2).setCellStyle(style);
                row.getCell(3).setCellStyle(style);
                row.getCell(4).setCellStyle(style);
            }
            CellRangeAddress region = new CellRangeAddress(list.size()+1, list.size()+1, 1, 4); // 合并第一行第一列到第二列
            sheetAt.addMergedRegion(region);
            Row row = sheetAt.createRow(list.size()+1);
            row.createCell(0).setCellValue("金额总计：");
            row.createCell(1).setCellValue(sumPrice);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            // 创建一个字体
            Font font = wb.createFont();
            font.setBold(true); // 设置字体加粗

            // 创建一个单元格样式，并将字体应用于它
            short cellIndex = 1;
            //CellStyle style = wb.getCellStyleAt(cellIndex);
            style.setFont(font);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void export(HttpServletResponse response, List<Accountant> list) {


        try {
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/accountant.xlsx");
            InputStream inputStream = resource.getInputStream();

            Workbook wb = new XSSFWorkbook(inputStream);

            Integer commissionSum = 0;
            Integer insuranceSum = 0;
            Integer incomeSum = 0;
            Integer operateSum = 0;
            Integer vehicleSum = 0;
            Integer loadingsSum = 0;
            Integer expenditureSum = 0;
            Integer profitSum = 0;
            Integer returnIncome = 0;
            Integer totalFreight = 0;
            Integer companyClaimsSum = 0;
            Integer branchClaimsSun = 0;

            CellStyle style = wb.createCellStyle();
            // 设置边框样式
            style.setBorderBottom((short) 1);
            style.setBorderBottom((short) 1);
            style.setBorderLeft((short) 1);
            style.setBorderRight((short) 1);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            Sheet sheetAt = wb.getSheetAt(0);
            wb.setSheetName(0,"收支统计");
            Sheet sheetAt1 = wb.getSheetAt(1);
            wb.setSheetName(1,"比例分析");
            for (int i = 0; i < list.size(); i++) {

                /**
                 * sheet1数据处理
                 */
                Row row = sheetAt.createRow(i+4);
                Accountant accountant = list.get(i);
                row.createCell(0).setCellValue(accountant.getAname());
                row.createCell(1).setCellValue(accountant.getCommission());
                commissionSum = commissionSum + accountant.getCommission();

                row.createCell(2).setCellValue(accountant.getInsurance());
                insuranceSum = insuranceSum + accountant.getInsurance();

                row.createCell(3).setCellValue(accountant.getCommission()+accountant.getInsurance());
                incomeSum = incomeSum + accountant.getCommission()+accountant.getInsurance();

                row.createCell(4).setCellValue(accountant.getOperate());
                operateSum = operateSum + accountant.getOperate();

                row.createCell(5).setCellValue(accountant.getVehicle());
                vehicleSum = vehicleSum + accountant.getVehicle();

                row.createCell(6).setCellValue(accountant.getLoadings());
                loadingsSum = loadingsSum + accountant.getLoadings();

                row.createCell(7).setCellValue(accountant.getOperate()+accountant.getVehicle()+accountant.getLoadings());
                expenditureSum = expenditureSum + accountant.getOperate()+accountant.getVehicle()+accountant.getLoadings();

                row.createCell(8).setCellValue((accountant.getCommission()+accountant.getInsurance())-(accountant.getOperate()+accountant.getVehicle()+accountant.getLoadings()));
                profitSum = profitSum + (accountant.getCommission()+accountant.getInsurance())-(accountant.getOperate()+accountant.getVehicle()+accountant.getLoadings());

                row.createCell(9).setCellValue(accountant.getReturnIncome());
                returnIncome = returnIncome + accountant.getReturnIncome();

                row.createCell(10).setCellValue(accountant.getTotalFreight());
                totalFreight = totalFreight + accountant.getTotalFreight();

                row.getCell(0).setCellStyle(style);
                row.getCell(1).setCellStyle(style);
                row.getCell(2).setCellStyle(style);
                row.getCell(3).setCellStyle(style);
                row.getCell(4).setCellStyle(style);
                row.getCell(5).setCellStyle(style);
                row.getCell(6).setCellStyle(style);
                row.getCell(7).setCellStyle(style);
                row.getCell(8).setCellStyle(style);
                row.getCell(9).setCellStyle(style);
                row.getCell(10).setCellStyle(style);


                /**
                 * sheet2数据处理
                 */
                Row row1 = sheetAt1.createRow(i+2);
                row1.createCell(0).setCellValue(accountant.getAname());
                row1.createCell(1).setCellValue(accountant.getTotalFreight());
                row1.createCell(2).setCellValue(accountant.getVehicle());

                BigDecimal originalValue = new BigDecimal((float)accountant.getVehicle() / accountant.getTotalFreight());
                BigDecimal roundedValue = originalValue.setScale(2, RoundingMode.HALF_UP);
                row1.createCell(3).setCellValue(roundedValue+"%");
                row1.createCell(4).setCellValue(accountant.getCompanyClaims());
                companyClaimsSum = companyClaimsSum + accountant.getCompanyClaims();

                row1.createCell(5).setCellValue(accountant.getBranchClaims());
                branchClaimsSun = branchClaimsSun + accountant.getBranchClaims();

                row1.createCell(6).setCellValue(accountant.getCompanyClaims()+accountant.getBranchClaims());

                row1.getCell(0).setCellStyle(style);
                row1.getCell(1).setCellStyle(style);
                row1.getCell(2).setCellStyle(style);
                row1.getCell(3).setCellStyle(style);
                row1.getCell(4).setCellStyle(style);
                row1.getCell(5).setCellStyle(style);
                row1.getCell(6).setCellStyle(style);


            }
            if(list.size()<=0){
                return;
            }
            Row row = sheetAt.createRow(list.size()+4);
            row.createCell(0).setCellValue("合计");
            row.createCell(1).setCellValue(commissionSum);
            row.createCell(2).setCellValue(insuranceSum);
            row.createCell(3).setCellValue(incomeSum);
            row.createCell(4).setCellValue(operateSum);
            row.createCell(5).setCellValue(vehicleSum);
            row.createCell(6).setCellValue(loadingsSum);
            row.createCell(7).setCellValue(expenditureSum);
            row.createCell(8).setCellValue(profitSum);
            row.createCell(9).setCellValue(returnIncome);
            row.createCell(10).setCellValue(totalFreight);

            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            row.getCell(2).setCellStyle(style);
            row.getCell(3).setCellStyle(style);
            row.getCell(4).setCellStyle(style);
            row.getCell(5).setCellStyle(style);
            row.getCell(6).setCellStyle(style);
            row.getCell(7).setCellStyle(style);
            row.getCell(8).setCellStyle(style);
            row.getCell(9).setCellStyle(style);
            row.getCell(10).setCellStyle(style);

            Row row1 = sheetAt1.createRow(list.size()+2);
            row1.createCell(0).setCellValue("合计");
            row1.createCell(1).setCellValue(totalFreight);
            row1.createCell(2).setCellValue(vehicleSum);
            BigDecimal originalValue = new BigDecimal((float)vehicleSum / totalFreight);
            BigDecimal roundedValue = originalValue.setScale(2, RoundingMode.HALF_UP);
            row1.createCell(3).setCellValue(roundedValue+"%");
            row1.createCell(4).setCellValue(companyClaimsSum);
            row1.createCell(5).setCellValue(branchClaimsSun);
            row1.createCell(6).setCellValue(companyClaimsSum+branchClaimsSun);

            row1.getCell(0).setCellStyle(style);
            row1.getCell(1).setCellStyle(style);
            row1.getCell(2).setCellStyle(style);
            row1.getCell(3).setCellStyle(style);
            row1.getCell(4).setCellStyle(style);
            row1.getCell(5).setCellStyle(style);
            row1.getCell(6).setCellStyle(style);

            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) {
        System.out.println((float)22/78);
        BigDecimal originalValue = new BigDecimal((float)22/78);
        BigDecimal roundedValue = originalValue.setScale(2, RoundingMode.HALF_UP);
        System.out.println(roundedValue+"%"); // 输出结果: 3.14
    }


    public static void downloadTemplate(HttpServletResponse response) {

        try {
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/importAccountant.xlsx");
            InputStream inputStream = resource.getInputStream();

            Workbook wb = new XSSFWorkbook(inputStream);
            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

}
